﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Xml.Linq;
using NPOI;
using NPOI.HSSF;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace ConvertToExcel
{
    public class ExcelImpl
    {
        /// <summary>
        /// 委托 Return a message to MainForm
        /// </summary>
        public NoticeMessage noticeMessage { get; set; }

        private ExcelTemp _Template;

        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <param name="data"></param>
        /// <param name="templatePath"></param>
        /// <param name="exporPath"></param>
        /// <param name="temp"></param>
        /// <returns></returns>
        public bool ExporExcel(IList<Part> data,string templatePath,string exporPath,ExcelTemp temp=ExcelTemp.TempAB)
        {
            this._Template = temp;
            try
            {
                //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
                HSSFWorkbook hssfworkbook;
                using (FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                    file.Close();
                }
                IList<PartRow> partRows=this.ParsePartRow(data);
                //create a entry of DocumentSummaryInformation
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "Jiangzm&Zhouming Team";
                hssfworkbook.DocumentSummaryInformation = dsi;
                //create a entry of SummaryInformation
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "Csvtoexcel Example";
                hssfworkbook.SummaryInformation = si;
                string name = data[0].PartName.Substring(0, data[0].PartName.IndexOf('_'));//T201
                if (_Template == ExcelTemp.TempAB)
                {
                    ISheet coverA = hssfworkbook.GetSheetAt(0);
                    ISheet sheet1 = hssfworkbook.GetSheetAt(1);
                    ISheet coverB = hssfworkbook.GetSheetAt(2);
                    ISheet sheet3 = hssfworkbook.GetSheetAt(3);

                    /**
                     * 
                     *  Write data of Partlist to ths sheet1
                     */
                    //create cell on rows, since rows do already exist,it's not necessary to create rows again.
                    int x = 0;
                    int y = 0;
                    ISheet currentSheet1 = sheet1;
                    ISheet currentSheet2 = sheet3;
                    bool hasTempA = false;
                    bool hasTempB = false;
                    double weightCountA = 0;
                    double weightCountB = 0;
                    foreach (var d in partRows)
                    {
                        switch (d.Code[0])
                        {
                            case 'A':
                                if (!hasTempA) hasTempA = true;
                                weightCountA += d.TotalWeight;
                                
                                int sheet1index = x % 32;
                                if (x > 0 && sheet1index == 0)
                                {
                                    currentSheet1 = hssfworkbook.CloneSheet(1);
                                    currentSheet1.ClearCells(new Region(4, 0, currentSheet1.LastRowNum, 9));
                                    currentSheet1.GetRow(1).GetCell(3).SetFormatValue(name);     // "{0}分段外购件托盘表"
                                    currentSheet1.GetRow(1).GetCell(4).SetFormatValue(name);     // "FS10012-{0}-01M-02"
                                }
                                currentSheet1.GetRow(sheet1index + 4).GetCell(0).SetCellValue(x + 1);
                                currentSheet1.GetRow(sheet1index + 4).GetCell(1).SetCellValue(d.Code);
                                currentSheet1.GetRow(sheet1index + 4).GetCell(2).SetCellValue(d.Name);
                                //currentSheet1.GetRow(sheet1index + 4).GetCell(3).SetCellValue(d.Description.Substring(d.Description.IndexOf(',') + 1));
                                currentSheet1.GetRow(sheet1index + 4).GetCell(5).SetCellValue(d.Number);
                                currentSheet1.GetRow(sheet1index + 4).GetCell(6).SetCellValue(d.Material);
                                currentSheet1.GetRow(sheet1index + 4).GetCell(7).SetCellValue(d.SingleWeight);
                                currentSheet1.GetRow(sheet1index + 4).GetCell(8).SetCellValue(d.TotalWeight);
                                x++;
                                
                                break;
                            case 'B':
                                if (!hasTempB) hasTempB = true;
                                weightCountB +=  d.TotalWeight;
                                
                                int sheet2index = y % 32;
                                if (y > 0 && sheet2index == 0)
                                {
                                    currentSheet2 = hssfworkbook.CloneSheet(3);
                                    currentSheet2.ClearCells(new Region(4, 0, currentSheet1.LastRowNum, 9));
                                    currentSheet2.GetRow(1).GetCell(4).SetFormatValue(name);
                                    currentSheet2.GetRow(1).GetCell(5).SetFormatValue(name);
                                }
                                currentSheet2.GetRow(sheet2index + 4).GetCell(0).SetCellValue(y + 1);
                                currentSheet2.GetRow(sheet2index + 4).GetCell(1).SetCellValue(d.Code);
                                currentSheet2.GetRow(sheet2index + 4).GetCell(2).SetCellValue(d.Name);
                                //currentSheet1.GetRow(sheet1index + 4).GetCell(3).SetCellValue(d.Description.Substring(d.Description.IndexOf(',') + 1));
                                currentSheet2.GetRow(sheet2index + 4).GetCell(5).SetCellValue(d.Number);
                                currentSheet2.GetRow(sheet2index + 4).GetCell(6).SetCellValue(d.Material);
                                currentSheet2.GetRow(sheet2index + 4).GetCell(7).SetCellValue(d.SingleWeight);
                                currentSheet2.GetRow(sheet2index + 4).GetCell(8).SetCellValue(d.TotalWeight);

                                y++;
                                break;
                            case 'C':
                            default:
                                break;
                        }
                    }
                    // A
                    if (hasTempA)
                    {
                        coverA.GetRow(32).GetCell(33).SetFormatValue(name);// "{0}分段舾装件外购件清单"
                        coverA.GetRow(34).GetCell(33).SetFormatValue(name);               // "FS10012-{0}-01M-01"
                        coverA.GetRow(32).GetCell(43).SetCellValue(weightCountA);
                        sheet1.GetRow(1).GetCell(4).SetFormatValue(name);     // "{0}分段外购件托盘表"
                        sheet1.GetRow(1).GetCell(5).SetFormatValue(name);     // "FS10012-{0}-01M-02"
                    }
                    // B
                    if (hasTempB)
                    {
                        coverB.GetRow(32).GetCell(33).SetFormatValue(name);
                        coverB.GetRow(34).GetCell(33).SetFormatValue(name);
                        coverB.GetRow(32).GetCell(43).SetCellValue(weightCountB);
                        sheet3.GetRow(1).GetCell(4).SetFormatValue(name);
                        sheet3.GetRow(1).GetCell(5).SetFormatValue(name);
                    }
                }
                else if (_Template == ExcelTemp.TempC)
                {
                    ISheet Sheet1 = hssfworkbook.GetSheetAt(0);
                    ISheet Sheet2 = hssfworkbook.GetSheetAt(1);
                    int z = 0;
                    double weightCount = 0;
                    foreach (var d in partRows)
                    {
                        if (d.Code[0]=='C')
                        {
                            weightCount += d.TotalWeight;
                            int sheet1index = z % 47;
                            if (z > 0 && sheet1index == 0)
                            {
                                Sheet2 = hssfworkbook.CloneSheet(1);
                                Sheet2.ClearCells(new Region(4, 0, Sheet2.LastRowNum, 7));
                                Sheet2.GetRow(0).GetCell(0).SetFormatValue(name); //"{0}分段制作件托盘表"
                                Sheet2.GetRow(1).GetCell(3).SetFormatValue(name); //"FS10012-{0}-01M-01"
                            }
                            Sheet2.GetRow(sheet1index + 4).GetCell(0).SetCellValue(z + 1);
                            if (String.IsNullOrEmpty(d.Name))
                            {
                                Sheet2.GetRow(sheet1index + 4).GetCell(1).SetCellValue("代号:"+d.Code);
                                Sheet2.GetRow(sheet1index + 4).GetCell(7).SetCellValue("割制或堆焊");
                            }
                            else
                            {
                                Sheet2.GetRow(sheet1index + 4).GetCell(1).SetCellValue(d.Name);
                                Sheet2.GetRow(sheet1index + 4).GetCell(2).SetCellValue(d.Specificat);
                                Sheet2.GetRow(sheet1index + 4).GetCell(3).SetCellValue(d.Material);
                            }
                            //Sheet2.ForceFormulaRecalculation = true;
                            z++;
                        }
                    }
                    Sheet1.GetRow(45).GetCell(7).SetFormatValue(name);       //"{0}舾装件制作材料清单"
                    Sheet1.GetRow(47).GetCell(7).SetFormatValue(name);      //"FS10012-{0}-01M-02"
                    Sheet1.GetRow(47).GetCell(9).SetCellValue(weightCount);
                    hssfworkbook.GetSheetAt(1).GetRow(0).GetCell(0).SetFormatValue(name);        //"{0}分段制作件托盘表"
                    hssfworkbook.GetSheetAt(1).GetRow(1).GetCell(3).SetFormatValue(name);        //"FS10012-{0}-01M-01"
                }

                //Write the stream data of workbook to the root directory
                using (FileStream exportFile = new FileStream(exporPath, FileMode.Create))
                {
                    hssfworkbook.Write(exportFile);
                    exportFile.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                noticeMessage("error:"+ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Get PartName from xml
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public string GetPartNameByCode(string code)
        {
            string Xmlpath = AppDomain.CurrentDomain.BaseDirectory + "\\PartName.xml";
            try
            {
                if (File.Exists(Xmlpath))
                {
                    XDocument xml = XDocument.Load(Xmlpath);
                    XElement xe = xml.Root.Elements().Where(m => m.Attribute("code").Value == code).FirstOrDefault();
                    if (xe != null)
                    {
                        return xe.Value;
                    }
                }
            }
            catch(Exception ex)
            {
                noticeMessage("error:" + ex.Message);
            }
            return String.Empty;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public IList<PartRow> ParsePartRow(IList<Part> data)
        {
            if (_Template == ExcelTemp.TempAB)
            {
                Dictionary<string, PartRow> rows = new Dictionary<string, PartRow>();
                foreach (var d in data)
                {
                    string[] strs = d.PartName.Split('_','-');
                    if (strs.Length < 3) continue;
                    if (strs[1][0] == 'A' || strs[1][0] == 'B')
                    {
                        string PartCode = strs[1] + "_" + strs[2] + "_" + strs[3];
                        string NameCode = strs[2];
                        if (rows.ContainsKey(PartCode))
                        {
                            PartRow pr = rows[PartCode];
                            pr.Number++;
                            pr.TotalWeight = pr.SingleWeight * pr.Number;
                        }
                        else
                        {
                            PartRow pr = new PartRow();
                            pr.Code = PartCode;
                            pr.Name = GetPartNameByCode(NameCode);
                            pr.Material = String.IsNullOrEmpty(d.Description) ? String.Empty : d.Description.Substring(0, d.Description.IndexOf(','));
                            pr.SingleWeight = d.Weight;
                            pr.Number = 1;
                            pr.TotalWeight = d.Weight;

                            rows.Add(PartCode, pr);
                        }
                    }
                }
                return rows.Values.ToList();
            }
            else
            {
                IList<PartRow> rows = new List<PartRow>();
                foreach (var d in data)
                {
                    string[] strs = d.PartName.Split('_','-');
                    if (strs[1][0] == 'C')
                    {
                        string[] des = d.Description.Split(',');
                        PartRow pr = new PartRow();
                        pr.Code = String.Format(strs[1] + "_" + strs[2] + "_" + strs[3]);
                        pr.Name = (des != null && des.Length > 0) ? des[0] : String.Empty;
                        pr.Specificat = (des != null && des.Length > 1) ? des[1] : String.Empty;
                        pr.Material = (des != null && des.Length > 2) ? des[2] : String.Empty;
                        pr.SingleWeight = d.Weight;

                        rows.Add(pr);
                    }
                }
                return rows;
            }
        }
    }

    public static class ExcelExtend
    {
        /// <summary>
        /// 扩展方法 Clear Region cells on the sheet
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="region"></param>
        public static void ClearCells(this ISheet sheet, Region region)
        {
            for (int i = region.RowFrom; i <= region.RowTo; i++)
            {
                for (int j = region.ColumnFrom; j <= region.ColumnTo; j++)
                {
                    sheet.GetRow(i).GetCell(j).CellFormula = String.Empty;
                    sheet.GetRow(i).GetCell(j).SetCellValue(String.Empty);
                }
            }
        }

        /// <summary>
        /// 扩展方法 Set a format string value for the cell
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="args"></param>
        public static void SetFormatValue(this ICell cell, params object[] args)
        {
            cell.SetCellValue(String.Format(cell.StringCellValue, args));
        }
    }

    public enum ExcelTemp
    {
        TempAB,
        TempC
    }

    public delegate void NoticeMessage(string msg); 
}
